package com.geyue.common.util;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.nutz.lang.util.NutMap;
import org.slf4j.Logger;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * 导出excle工具类
 *
 * @author lfei
 */
public class ExcelUtil {

    /**
     * 不在服务器上保存文件， 写回xls文件给用户
     *
     * @param request
     * @param response
     * @param excelData
     * @throws Exception
     */
    public static void download(HttpServletRequest request, HttpServletResponse response, ExcelBean excelData) throws Exception {
        Object[] datas = null;
        String fileName = null;
        if (excelData.getSheets() != null) {
            datas = excelData.getSheets().values().toArray();
            Arrays.sort(datas);
        } else {
            datas = new Object[]{excelData};
        }
        HSSFWorkbook excel = ExcelUtil.createExcel(datas);
        // 生成文件
        HSSFSheet sheet = excel.getSheetAt(0);
        HSSFFooter footer = sheet.getFooter();
        footer.setRight("Page " + HSSFFooter.page() + " of "
                + HSSFFooter.numPages());
        response.setContentType("application/x-msdownload;charset=iso-8859-1");
        fileName = new String(excelData.getName().trim().getBytes("GBK"), "iso-8859-1") + ".xls";
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

        OutputStream sops = response.getOutputStream();// 不同类型的文件对应不同的MIME类型

        excel.write(sops);
        sops.flush();
        sops.close();
    }

    /**
     * 创建excel
     *
     * @param datas
     * @return
     */
    public static HSSFWorkbook createExcel(Object[] datas) throws Exception {
        HSSFWorkbook excel = new HSSFWorkbook();
        for (Object o : datas) {
            ExcelBean data = (ExcelBean) o;
            // 创建sheet
            HSSFSheet sheet = excel.createSheet(data.getSheetName());
            // 创建表头
            HSSFHeader header = sheet.getHeader();
            header.setCenter(data.getHeaderCenter());
            HSSFRow headerRow = sheet.createRow(0);

            // 设置单元格类型
            HSSFCellStyle cellStyle = excel.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直布局：居中
            cellStyle.setWrapText(true);

            for (int i = 0; i < data.getTableHeader().length; i++) {
                HSSFCell headerCell = headerRow.createCell(i);
                headerCell.setCellStyle(cellStyle); // 设置单元格样式
                headerCell.setCellValue(data.getTableHeader()[i].trim());
            }

            // 创建数据
            int rowIndex = 1;
            for (Object sheetData[] : data.getSheetData()) {
                HSSFRow row = sheet.createRow(rowIndex++);
                for (int i = 0; i < sheetData.length; i++) {
                    // 创建第i个单元格
                    HSSFCell cell = row.createCell(i);
                    cell.setCellStyle(cellStyle); // 设置单元格样式
                    if (sheetData[i] == null) {
                        cell.setCellValue("");
                    } else if ((sheetData[i] + "").matches("^\\d+(.{1}\\d+)?E?$")
                            && (!(sheetData[i] + "").equals(""))) {
                        cell.setCellValue((sheetData[i] + ""));
                    } else {
                        cell.setCellValue(sheetData[i] + "");
                    }
                }
            }


            // 合并单元格
            if (data.getColRow() != null) {
                for (int i = 0; i < data.getColRow().size(); i++) {
                    Integer[] colr = data.getColRow().get(i);
                    sheet.addMergedRegion(new Region((short) colr[0]
                            .shortValue(), (short) colr[1].shortValue(),
                            (short) colr[2].shortValue(), (short) colr[3]
                            .shortValue()));
                }
            }
            autoSizeColumn(sheet, data);
        }

        return excel;
    }

    /**
     * 创建带单元格样式excel
     *
     * @param datas
     * @return
     */
    public static HSSFWorkbook createExcelStyle(Object[] datas) throws Exception {
        HSSFWorkbook excel = new HSSFWorkbook();
        // 设置单元格类型
        HSSFCellStyle cellStyle = excel.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中 
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直布局：居中 
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setWrapText(true);

        // 设置单元格类型 
        HSSFCellStyle cellStylerow = excel.createCellStyle();
        cellStylerow.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
        cellStylerow.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStylerow.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStylerow.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStylerow.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//右居

        //设置标题样式
        HSSFCellStyle cellStyletitle = excel.createCellStyle();
        cellStyletitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
        cellStyletitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直布局：居中 
        cellStyletitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyletitle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyletitle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyletitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//右居
        HSSFFont font = excel.createFont();
        font.setFontHeightInPoints((short) 14);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);         //字体增粗
        //把字体应用到当前的样式
        cellStyletitle.setFont(font);

        for (Object o : datas) {
            ExcelBean data = (ExcelBean) o;
            // 创建sheet
            HSSFSheet sheet = excel.createSheet(data.getSheetName());
            //创建标题
            HSSFRow row = sheet.createRow(0);
            row.setHeightInPoints(30);
            HSSFCell celltitle = null;
            for (int m = 0; m < data.getColLength(); m++) {

                celltitle = row.createCell(m);
                if (m == 0) celltitle.setCellValue(new HSSFRichTextString(data.getHeaderCenter()));
                celltitle.setCellStyle(cellStyletitle);
            }

            //合并单元格
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, data.getColLength() - 1);
            sheet.addMergedRegion(cellRangeAddress);
            //创建第二行
            row = sheet.createRow(1);
            row.setHeightInPoints(20);
            HSSFCell cellt = null;
            for (int m = 0; m < data.getColLength(); m++) {
                cellt = row.createCell(m);
                if (m == 0) cellt.setCellValue(data.getTodate());
                cellt.setCellStyle(cellStylerow);
            }
            //合并单元格
            cellRangeAddress = new CellRangeAddress(1, 1, 0, data.getColLength() - 1);
            sheet.addMergedRegion(cellRangeAddress);

            // 创建表头
            HSSFHeader header = sheet.getHeader();
            header.setCenter(data.getHeaderCenter());
            HSSFRow headerRow = sheet.createRow(2);
            headerRow.setHeightInPoints(30);
            for (int i = 0; i < data.getTableHeader().length; i++) {
                HSSFCell headerCell = headerRow.createCell(i);
                headerCell.setCellStyle(cellStyle); // 设置单元格样式
                headerCell.setCellValue(data.getTableHeader()[i].trim());
            }

            // 创建数据
            int rowIndex = 3;
            for (Object sheetData[] : data.getSheetData()) {
                row = sheet.createRow(rowIndex++);
                row.setHeightInPoints(30);
                for (int i = 0; i < sheetData.length; i++) {
                    // 创建第i个单元格
                    HSSFCell cell = row.createCell(i);
                    cell.setCellStyle(cellStyle); // 设置单元格样式
                    if (sheetData[i] == null) {
                        cell.setCellValue("");
                    } else if ((sheetData[i] + "").matches("^\\d+(.{1}\\d+)?E?$")
                            && (!(sheetData[i] + "").equals(""))) {
                        cell.setCellValue(sheetData[i] + "");
                    } else {
                        cell.setCellValue(sheetData[i] + "");
                    }
                }
            }
            int lastRow = rowIndex++;
            row = sheet.createRow(lastRow);
            HSSFCell celllast = null;
            for (int m = 0; m < data.getColLength(); m++) {
                celllast = row.createCell(m);
                celllast.setCellStyle(cellStyle); // 设置单元格样式
                if (m == 0) celllast.setCellValue(data.getLastValue());
            }

            //合并单元格
            cellRangeAddress = new CellRangeAddress(lastRow, lastRow, 0, data.getColLength() - 1);
            sheet.addMergedRegion(cellRangeAddress);
            // 合并单元格
            if (data.getColRow() != null) {
                for (int i = 0; i < data.getColRow().size(); i++) {
                    Integer[] colr = data.getColRow().get(i);
                    sheet.addMergedRegion(new Region((short) colr[0]
                            .shortValue(), (short) colr[1].shortValue(),
                            (short) colr[2].shortValue(), (short) colr[3]
                            .shortValue()));
                }
            }
            autoSizeColumn(sheet, data);
        }

        return excel;
    }

    /**
     * 列度自适应
     *
     * @param sheet
     */
    private static void autoSizeColumn(HSSFSheet sheet, ExcelBean data) throws Exception {
        if (data.getTableHeader() != null && data.getTableHeader().length > 0) {
            int length = data.getTableHeader().length - 1;
            Integer[] widths = data.getColWidth();
            if (widths == null) {
                while (length >= 0) {
                    sheet.autoSizeColumn((short) length); // 调整列宽度
                    --length;
                }
            } else {
                while (length >= 0) {
                    sheet.setColumnWidth(length, widths[length]);
                    --length;
                }
            }
        }

    }

    /**
     * 控制层跳转错误页面(/500)
     *
     * @param response
     * @param request
     * @param toPage
     */
    public static void toErrorPage(HttpServletResponse response, HttpServletRequest request, String toPage) {
        try {
            response.sendRedirect(request.getContextPath() + toPage);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 批量设置单元格格式
     *
     * @param sheet     表sheet对象
     * @param cellstyle 设置的style
     * @param startRow  开始的行号
     * @param startCell 开始的列号
     * @param endRow    结束的行号
     * @param endCell   结束的列号
     *                  void
     */
    public static void setCellStyle(Sheet sheet, CellStyle cellstyle,
                                    int startRow, int startCell, int endRow, int endCell) {
        for (int i = startRow; i <= endRow; i++) {
            Row row = sheet.getRow(i);
            for (int j = startCell; j <= endCell; j++) {
                Cell cell = row.getCell(j);
                cell.setCellStyle(cellstyle);
            }
        }
    }

    /**
     * 创建一个Font
     *
     * @param workbook 表空间
     * @param fontName 字体
     * @return Font
     */
    public static Font createFont(Workbook workbook, String fontName) {
        Font font = workbook.createFont();
        if (fontName != null)
            font.setFontName(fontName);
        return font;
    }

    /**
     * 创建一个Font
     *
     * @param workbook 表空间
     * @param fontSize 字体大小
     * @return Font
     */
    public static Font createFont(Workbook workbook, Short fontSize) {
        Font font = workbook.createFont();
        if (fontSize != null)
            font.setFontHeightInPoints(fontSize);
        return font;
    }

    /**
     * 创建一个Font
     *
     * @param workbook 表空间
     * @param fontName 字体
     * @param fontSize 字体大小
     * @param color    字体颜色
     * @return Font
     */
    public static Font createFont(Workbook workbook, String fontName, Short fontSize, Short color) {
        Font font = workbook.createFont();
        if (fontName != null)
            font.setFontName(fontName);
        if (fontSize != null)
            font.setFontHeightInPoints(fontSize);
        if (color != null)
            font.setColor(color);
        return font;
    }

    /**
     * 添加批注
     *
     * @param workbook
     * @param sheet
     * @param cell     单元格
     * @param text     批注
     *                 void
     */
    public static void addComment(Workbook workbook, Sheet sheet, Cell cell, String text) {
        CreationHelper newFactory = workbook.getCreationHelper();
        RichTextString str = newFactory.createRichTextString(text);
        Drawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = newFactory.createClientAnchor();
        anchor.setCol1(0);
        anchor.setCol2(3);
        anchor.setRow1(0);
        anchor.setRow2(6);
        Comment comment = drawing.createCellComment(anchor);
        comment.setString(str);
        cell.setCellComment(comment);
    }


    /**
     * 删除文件夹下文件名包含的ref的旧文件
     * referenceP 空 则删除非当日创建的文件
     *
     * @param path
     */
    public static void deleteOtherRefFile(String path, String referenceP) {
        String reference = null;
        if (StringUtils.isNotBlank(referenceP))
            reference = referenceP;
        else {
            Calendar calendar = Calendar.getInstance();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            reference = sdf.format(calendar.getTime());
        }
        File file = new File(path);
        if (file.isDirectory()) {
            String[] tempList = file.list();
            File temp = null;
            for (int i = 0; i < tempList.length; i++) {
                if (tempList[i].indexOf(reference) < 0) {
                    if (path.endsWith(File.separator)) {
                        temp = new File(path + tempList[i]);
                    } else {
                        temp = new File(path + File.separator + tempList[i]);
                    }
                    if (temp.isFile()) {
                        temp.delete();
                    }
                }
            }
        } else {
            file.mkdirs();
        }
    }

    /**
     * 上传错误信息到folderP文件夹
     * 并folderP删除文件夹下文件名包含的ref的旧文件
     * folderP /error
     * 写回http下载地址
     *
     * @param map
     * @param request
     */
    public static Map<String, Object> doBackImport(Map<String, Object> map, HttpServletRequest request, String folderP, String referenceP, Logger logger) {
        Map<String, Object> mapToJson = new HashMap<String, Object>();
        //String groupId = SessionUtil.getGroupId(request);
        XSSFWorkbook workBook = (XSSFWorkbook) map.get("wk");
        if (workBook != null) {
            String httpPath = request.getScheme() + "://"
                    + request.getServerName() + ":" + request.getServerPort()
                    + request.getContextPath() + "/";
            //上传路径
            String realPath = request.getSession().getServletContext().getRealPath(folderP);
            //删除非今天的文件
            String flodPath = realPath.substring(0, realPath.length());
            deleteOtherRefFile(flodPath, referenceP);
            String fileName = System.currentTimeMillis()+"";
            //删除原来产生的错误文件
            String newRealPath = realPath + File.separator + fileName + ".xlsx";
            String newHttpPath = httpPath + folderP.substring(1, folderP.length()) + "/" + fileName + ".xlsx";
            FileOutputStream fos = null;
            try {
                fos = new FileOutputStream(newRealPath);
                workBook.write(fos);
                fos.flush();
            } catch (Exception e) {
                e.printStackTrace();
                logger.error("doBackImport 返回" + newRealPath + "失败", e);
            } finally {
                if (fos != null) {
                    try {
                        fos.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                        logger.error("doBackImport 返回" + newRealPath + "失败", e);
                    }
                }
            }
            mapToJson.put("path", newHttpPath);
        }
        mapToJson.put("success", true);
        mapToJson.put("total", map.get("total"));
        mapToJson.put("right", map.get("right"));
        mapToJson.put("error", map.get("error"));
        return mapToJson;
    }

    public static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {

        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容    
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
        // DVConstraint constraint = new DVConstraint();    
        constraint.setExplicitListValues(textList);

        // 设置数据有效性加载在哪个单元格上。    
        // 四个参数分别是：起始行、终止行、起始列、终止列    
        CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);

        // 数据有效性对象    
        DataValidation data_validation = helper.createValidation(constraint, regions);
        //DataValidation data_validation = new DataValidation(regions, constraint);    

        return data_validation;
    }

    private final static String xls = "xls";
    private final static String xlsx = "xlsx";

    /**
     * 读入excel文件，解析后返回List<Map>
     * @param file
     * @throws IOException
     */
    public static List<NutMap> readExcel(MultipartFile file) throws IOException {
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<NutMap> list = new ArrayList<>();
        if(workbook != null){
            for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if(sheet == null){
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum  = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环除了第一行的所有行
                for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if(row == null){
                        continue;
                    }
                    NutMap obj = new NutMap();
                    obj.put("id",getCellValue(row.getCell(0)));
                    obj.put("isOk",getCellValue(row.getCell(11)));
                    obj.put("because",getCellValue(row.getCell(12)));
                    list.add(obj);
                }
            }
        }
        return list;
    }

    /**
     * 检测
     * @param file
     * @throws IOException
     */
    public static void checkFile(MultipartFile file) throws IOException{
        //判断文件是否存在
        if(null == file){
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
            throw new IOException(fileName + "不是excel文件");
        }
    }

    /**
     * 得到工作簿
     * @param file
     * @return
     */
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(fileName.endsWith(xls)){
                //2003
                workbook = new HSSFWorkbook(is);
            }else if(fileName.endsWith(xlsx)){
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
        }
        return workbook;
    }

    /**
     * 转换为字符串类型返回
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}
